﻿using System;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;
using System.Collections.Generic;


public class Query
{
    private string connString = DatabaseConcept.Properties.Settings.Default.connectionString;
    private static Query instance;
    public const int Single = 1; 

    private Query() 
    {
            
    }

    public static Query Instance
   {
      get 
      {
         if (instance == null)
         {
             instance = new Query();
         }
         return instance;
      }
   }

    /// <summary>
    /// Creates a team and adds it to the Database.
    /// </summary>
    /// <param name="name">Name of the team to add</param>
    /// <param name="member_id">List of members who will initially be on the Team</param>
    public void CreateTeam(string name, List<string> member_id)
    {

        if (member_id.Count > 0)
        {
            string query = String.Format("insert into Team(name) values" +
                                           " ('{0}')", name);

            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = query;
            int id = -1;
            try
            {
                conn.Open();
                command.ExecuteNonQuery();
                query = String.Format("SELECT LAST_INSERT_ID()");
                command.CommandText = query;
                MySqlDataReader reader = command.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    id = reader.GetInt32(0);
                }
                conn.Close();
                conn.Open();
                for (int i = 0; i < member_id.Count; i++)
                {
                    query = String.Format("insert into User_Plays_For_Team(Team_team_id, User_user_id) "
                            + "values('{0}', '{1}')", id.ToString(), member_id[i]);
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);

            }

            conn.Close();
        }
        else
        {
            MessageBox.Show("Team must have at least 1 member.");
        }
    }

    /// <summary>
    /// Creates an Event and adds it to the Database
    /// </summary>
    /// <param name="name">The name of the event</param>
    /// <param name="date">The date the event occurs on</param>
    /// <param name="place">Where the event occurs</param>
    public void CreateEvent(string name, string date, string place)
    {
        string query = "insert into Event(event_name, date, place)" +
                       " values ('" + name + "', '" + date + "', '" + place + "')";
     
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;    

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");
            conn.Close();
        }
        conn.Close();
        
    }
    /// <summary>
    /// Creates a Tournament and adds it to the Database
    /// </summary>
    /// <param name="name">The name of the tournament</param>
    /// <param name="prize">The tournament's prize</param>
    /// <param name="Game_id">ID relating the tournament to the game being played in it</param>
    /// <param name="elimType">Relates the tournament to its elimination rules</param>
    /// <param name="event_id">Relates the tournament to the event it is part of</param>
    /// <param name="maxteams">The maximum number of teams allowed to participate</param>
    public void CreateTournament(string name, string prize, string Game_id, string elimType, string event_id, string maxteams)
    {
        string query = String.Format("insert into Tournament(Event_event_id, name, prize, Game_id, Elimination_type_elim_id, max_teams) values" +
                                        " ({0}, '{1}', '{2}', '{3}', '{4}', '{5}')", event_id, name, prize, Game_id, elimType, maxteams);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
           MessageBox.Show(ex.Message);

        }

        conn.Close();
    }

    /// <summary>
    /// Creates a member and adds it to the Database
    /// </summary>
    /// <param name="name">The name of the member</param>
    /// <param name="email">The member's email</param>
    /// <param name="pwd">The member's password</param>
    public void CreateMember(string name, string email, string pwd) {

        string query = String.Format("insert into User(name, email, password) values" +
                                     "('{0}', '{1}', '{2}')", name, email, pwd);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);

        }

        conn.Close();
    }

    /// <summary>
    /// Creates a ticket and adds it to the Database
    /// </summary>
    /// <param name="name">The name of the person buying the ticket</param>
    /// <param name="event_id">The ID of the event the ticket was sold for</param>
    /// <param name="type_id">The type of ticket that they bought</param>
    public void CreateTicket(string name, string event_id, string type_id)
    {
        string query = String.Format("insert into Ticket(name, TicketType_id) values" +
                                     "('{0}', '{1}')", name, type_id);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }
        conn.Close();

        int id = 1;
        command.CommandText = "SELECT LAST_INSERT_ID()";
        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            if (reader != null && reader.Read())
            {
                id = reader.GetInt32(0);
            }
        }catch (MySqlException ex)
        {
            Console.WriteLine(ex.Message);
        }
        conn.Close();

        query = String.Format("insert into TicketsSold(ticket_id, event_id) values" +
                                     "('{0}', '{1}')", id, event_id);

        conn = new MySqlConnection(connString);
        command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Console.WriteLine(ex.Message);

        }

        conn.Close();
    }

    /// <summary>
    /// Populates the Combo Box that adds members to teams
    /// </summary>
    /// <param name="members">The list of members to choose from</param>
    internal void populateTeamMembers(Dictionary<string, string> members)
    {
        string query = "Select user_id, name from User";
        members.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                members.Add(reader.GetString("user_id"), reader.GetString("name"));
             }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }


        conn.Close();
    }

    /// <summary>
    /// Populates the list of events
    /// </summary>
    /// <param name="events">The list of events</param>
    internal void populateEventDisctionary(Dictionary<string, string> events)
    {
        string query = "Select event_id, event_name from Event";
        events.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                events.Add(reader.GetString("event_id"), reader.GetString("event_name"));
                //event_lb.Items.Add(reader["event_name"].ToString());
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }


        conn.Close();
    }

    /// <summary>
    /// Populates the list of tournaments associated with an event
    /// </summary>
    /// <param name="tournamentsEvents">The list of tournaments</param>
    /// <param name="eventID">the ID of the event to get the tournaments for</param>
    internal void populateTournamentsEventDisctionary(Dictionary<string, string> tournamentsEvents, string eventID)
    {
        string query = String.Format("Select T.tourn_id, T.name from Tournament as T Join Event as E ON (T.Event_event_id = E.event_id) where (E.event_id = '{0}')", eventID);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;
        tournamentsEvents.Clear();
        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                tournamentsEvents.Add(reader.GetString("tourn_id"),reader.GetString("name"));
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");
        }
        finally
        {
            conn.Close();
        }
        
        
    }

    /// <summary>
    /// Populates the ticket type combo box
    /// </summary>
    /// <param name="ticketTypes">The list of types of tickets</param>
    internal void populateTicketTypes(Dictionary<string, string> ticketTypes)
    {
        string query = "Select type_id, type_name, price from TicketType";
        ticketTypes.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ticketTypes.Add(reader.GetString("type_id"), reader.GetString("type_name") + " - $" + reader.GetString("price"));
                //event_lb.Items.Add(reader["event_name"].ToString());
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }


        conn.Close();
    }

    /// <summary>
    /// Populates the combo box of elimination types
    /// </summary>
    /// <param name="elimTypes">the list of elimination types</param>
    internal void populateElimTypes(Dictionary<string, string> elimTypes)
    {
        string query = "SELECT elim_id, name FROM TournamentSystem.Elimination_type;";
        elimTypes.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                elimTypes.Add(reader.GetString("elim_id"), reader.GetString("name"));
                //event_lb.Items.Add(reader["event_name"].ToString());
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }


        conn.Close();
    }
    
    /// <summary>
    /// Populates the list of games in the listbox
    /// </summary>
    /// <param name="gameTypes">The list of game types to add to the listbox</param>
    internal void populateGames(Dictionary<string, string> gameTypes)
    {
        string query = "SELECT id, name, description FROM Game;";
        gameTypes.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                String description = reader.GetString("description");
                if(description.Length>45){
                    description = description.Substring(0,45)+"...";
                }
                gameTypes.Add(reader.GetString("id"), reader.GetString("name") + " - " + description);
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }

        conn.Close();
    }

    /// <summary>
    /// Creates a new game type
    /// </summary>
    /// <param name="name">The name of the game</param>
    /// <param name="description">A description of the game</param>
    internal void CrateGame(string name, string description)
    {
        string query = String.Format("insert into Game(name, description) values" +
                                     "('{0}', '{1}')", name, description);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);

        }

        conn.Close();
    }

    /// <summary>
    /// Deletes a game
    /// </summary>
    /// <param name="game_id">the ID of the game to delete</param>
    internal void RemoveGame(string game_id)
    {
        string tquery = String.Format("Select Count(tourn_id) as Rows from Tournament where Game_id='{0}' Group by Game_id;", game_id);
        MySqlConnection tconn = new MySqlConnection(connString);
        MySqlCommand tcommand = tconn.CreateCommand();
        tcommand.CommandText = tquery;

        tconn.Open();
        MySqlDataReader reader = tcommand.ExecuteReader();
        if (reader.HasRows)
        {
            MessageBox.Show("You must remove all the tournaments that are using this game before removing the game","Game still in use");
        }
        else
        {
            string query = String.Format("DELETE from Game where id='{0}'", game_id);

            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = query;

            try
            {
                conn.Open();
                command.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);

            }

            conn.Close();
        }
    }

    /// <summary>
    /// Populate a list of teams associated with a tournament
    /// </summary>
    /// <param name="teams">the list of teams</param>
    /// <param name="tourn_id">The tournament to list the teams for</param>
    internal void populateTeams(Dictionary<string, string> teams, String tourn_id)
    {
        string query = String.Format("SELECT T.team_id, T.name FROM TournamentSystem.Team_To_Participate_Tournament as TPT"+
            " Join Team As T On T.team_id=TPT.Team_team_id where TPT.Tournament_tourn_id='{0}';",tourn_id);
        teams.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                teams.Add(reader.GetString("team_id"), reader.GetString("name"));
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }

        conn.Close();
    }

    /// <summary>
    /// Adds a new match
    /// </summary>
    /// <param name="event_id">The ID of the event the match goes with</param>
    /// <param name="tourn_id">The ID of the tournament the match goes with</param>
    /// <param name="Team1">The first team in the match</param>
    /// <param name="Team2">The second team in the match</param>
    internal void addMatch(string event_id, string tourn_id, string Team1, string Team2)
    {
        string query = String.Format("insert into `Match`(Team1, Team2) values" +
                                     "('{0}', '{1}'); SELECT LAST_INSERT_ID()", Team1, Team2);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;
        
        try
        {
            conn.Open();
            long lid = (long)command.ExecuteScalar();
            command.ExecuteNonQuery();

            string query2 = String.Format("insert into Tournament_has_Match(Match_match_id, Tournament_tourn_id) values" +
                                     "('{0}', '{1}', '{2}')", lid, tourn_id);
            MySqlCommand command2 = conn.CreateCommand();
            command2.CommandText = query2;
            command2.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);

        }

        conn.Close();
    }

    /// <summary>
    /// Populates the list of matches associated with a tournament
    /// </summary>
    /// <param name="matchesList">The list of matches to populate from</param>
    /// <param name="tournID">The tournament ID to get matches for</param>
    internal void populateMatchesList(Dictionary<string, string> matchesList, string tournID)
    {
        string query = String.Format("SELECT match_id, Team1, Team2, Winner FROM ViewMatches where tid='{0}';", tournID);
        matchesList.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                String Team1 = "<NULL>";
                if(!reader.IsDBNull(1)){
                   Team1 = reader.GetString("Team1");
                }
                String Team2 = "<NULL>";
                if (!reader.IsDBNull(2))
                {
                    Team2 = reader.GetString("Team2");
                }
                String Winner = "<NULL>";
                if (!reader.IsDBNull(3))
                {
                    Team1 = reader.GetString("Winner");
                }
                matchesList.Add(reader.GetString("match_id"), String.Format("{0} vs {1} W:{2}",Team1, Team2, Winner));
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }
        conn.Close();
    }

    /// <summary>
    /// Populates the list of teams in a match
    /// </summary>
    /// <param name="teamsList">The list of teams</param>
    /// <param name="tournID">The ID of the tournament the match is in</param>
    /// <param name="matchID">The ID of the match to get the teams for</param>
    internal void populateMatchesTeams(Dictionary<string, string> teamsList, string tournID, string matchID)
    {
        string query = String.Format("SELECT Team1, Team1_id, Team2 , Team2_id FROM ViewMatches where tid='{0}' and match_id = '{1}';", tournID, matchID);
        teamsList.Clear();
        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                String Team1 = "<NULL>";
                if (!reader.IsDBNull(0))
                {
                    Team1 = reader.GetString("Team1");
                }
                String Team1_id = null;
                if (!reader.IsDBNull(1))
                {
                    Team1_id = reader.GetString("Team1_id");
                }

                String Team2 = "<NULL>";
                if (!reader.IsDBNull(2))
                {
                    Team2 = reader.GetString("Team2");
                }
                String Team2_id = null;
                if (!reader.IsDBNull(3))
                {
                    Team2_id = reader.GetString("Team2_id");
                }
                teamsList.Add(Team1_id, Team1);

                if(!teamsList.TryGetValue(Team2_id, out Team1)){
                    teamsList.Add(Team2_id, Team2);
                }
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "SQL Error");

        }
        conn.Close();
    }

    /// <summary>
    /// Updates the winner of a match
    /// </summary>
    /// <param name="match_id">The ID of the match to update</param>
    /// <param name="winner">The winner of the match</param>
    internal void UpdateMatchWinner(string match_id, string winner)
    {
        string query = String.Format("Update `Match` Set winner = '{0}' where match_id='{1}'", winner, match_id);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery() ;
            MessageBox.Show("Success");
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);

        }

        conn.Close();
    }

    /// <summary>
    /// Populates the View that shows ticket sales
    /// </summary>
    /// <param name="gridview">The DataGridView to be populated</param>
    internal void populateTicketSaleView(DataGridView gridview)
    {
        try
        {
            MySqlConnection conn = new MySqlConnection(connString);
            conn.Open();

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string query = "SELECT * from Ticket_View";
            MyDA.SelectCommand = new MySqlCommand(query, conn);

            DataTable table = new DataTable();
            MyDA.Fill(table);

            BindingSource bSource = new BindingSource();
            bSource.DataSource = table;

            gridview.DataSource = bSource;
            gridview.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "MySQL Error");
        }


    }

    /// <summary>
    /// Adds a team to a tournament
    /// </summary>
    /// <param name="tourn_id">The Tournament to add the team to</param>
    /// <param name="team_id">The ID of the team to add to the tournament</param>
    internal void AddTeamToTournament(string tourn_id, string team_id)
    {
        string query = String.Format("INSERT INTO `Team_To_Participate_Tournament` (`Tournament_tourn_id`, `Team_team_id`)" +
            "VALUES ('{0}','{1}');", tourn_id, team_id);

        MySqlConnection conn = new MySqlConnection(connString);
        MySqlCommand command = conn.CreateCommand();
        command.CommandText = query;

        try
        {
            conn.Open();
            command.ExecuteNonQuery();
            MessageBox.Show("Success");
        }
        catch (MySqlException ex)
        {
            if (ex.Message.Contains("'Tournament_tourn_id' cannot be null"))
            {
                MessageBox.Show("Max number of teams has already been reached");
            }
            else
            {
                MessageBox.Show(ex.Message);
            }

        }

        conn.Close();
    }
}
